package com.dgut.staffmodule.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.dgut.staffmodule.entity.Staff;

@Repository
public interface StaffRepository extends PagingAndSortingRepository<Staff, Long>, JpaSpecificationExecutor<Staff> {
	// 根据部门ID查出该部门的员工人数
	@Query(value = "select count(*) from t_staff WHERE department_id = :id", nativeQuery = true)
	public int findByDeptId(@Param("id") Long id);

	// 根据部门ID查找出该部门下的所有员工信息
	@Query(value = "select * from t_staff WHERE department_id = :id ", nativeQuery = true)
	public List<Staff> findByDeptId2(@Param("id") Long id);

	// 根据姓名查找员工
	@Query(value = "select * from t_staff WHERE staffName=:staffName", nativeQuery = true)
	public Staff findByStaffName(@Param("staffName") String staffName);

	// 根据职位名称查找出该职位与该部门的所有员工
	@Query(value = "select * from t_staff WHERE position = :position and department_id = :id", nativeQuery = true)
	public List<Staff> findByPos(@Param("position") String position, @Param("id") Long id);

	// 根据职位名称查找出该职位的所有员工信息
	@Query(value = "select * from t_staff WHERE position = :position ", nativeQuery = true)
	public List<Staff> findByPos(@Param("position") String position);

	// 找领导
	// 根据部门ID和职称找部门领导
	@Query(value = "select * from t_staff where department_id =:id and position like '%总监%'", nativeQuery = true)
	public Staff findDepartmentLeader(@Param("id") Long id);

	// 找人事部领导
	// 直接在用户表查询
	@Query(value = "select * from t_staff where position = '人事管理员'", nativeQuery = true)
	public Staff findHRLeader();

	// 统计性别
	@Query(value = "select count(*) from t_staff WHERE sex = :sex", nativeQuery = true)
	public int findBySex(@Param("sex") String sex);

	// 查找性别
	// 直接在用户表查询
	@Query(value = "select * from t_staff where sex = :sex", nativeQuery = true)
	public List<Staff> findBySex2(@Param("sex") String sex);

}
